﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IHISCETeim.Library;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using IHISCETeim.Model;


namespace IHISCETeim.DAL
{
    /// <summary>
    /// 1.应收账款主表(FTradeReceivables)
    /// </summary>
    public class FTradeReceivablesDAL
    {
        /// <summary>
        /// 添加应收账款
        /// </summary>
        /// <param name="objReceivables">应收账款主表(FTradeReceivables)</param>
        /// <param name="objSalesType">应收帐款销售类型更改记录表</param>
        /// <param name="listAgeDetails">应收账款帐龄明细表(FTradeAgeDetail)多个</param>
        /// <returns>
        /// 返回空表示成功,否则返回失败信息
        /// </returns>
        public static string Add(FTradeReceivablesInfo objReceivables, FTradeSalesTypeInfo objSalesType, List<FTradeAgeDetailInfo> listAgeDetails)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    //1.应收账款主表(FTradeReceivables)
                    DbCommand objReceivablesdbCommand = DB.GetStoredProcCommand("FTradeReceivables_Add", objReceivables.HeTongHao, objReceivables.XiaoShouShiJian, objReceivables.XingHao, objReceivables.JiHao, objReceivables.JingXiaoShang, objReceivables.YingYeJingLi, objReceivables.Price, objReceivables.GPSFeiYong, objReceivables.YueCuXiaoJinE, objReceivables.HeTongJinE, objReceivables.FuKuanTiaoKuan, objReceivables.DaoQiRi, objReceivables.XiaoShouLeiXing, objReceivables.GPSNum, objReceivables.Currency, objReceivables.CurrencyAmount, objReceivables.Rate, objReceivables.ZDYongHu,objReceivables.FanHuanLiXi,objReceivables.InterestIncome);
                    int FTradeID = Convert.ToInt32(DB.ExecuteScalar(objReceivablesdbCommand, trans));//1.主表ID
                    //2.应收帐款销售类型更改记录表
                    DbCommand objSalesTypedbCommand = DB.GetStoredProcCommand("FTradeSalesType_Add", FTradeID, objSalesType.XiaoShouLeiXing, objSalesType.ExConfig, objSalesType.State, objSalesType.RealName);
                    int FTradeSalesId = Convert.ToInt32(DB.ExecuteScalar(objSalesTypedbCommand, trans));
                    //3.应收账款帐龄明细表(FTradeAgeDetail)
                    foreach (FTradeAgeDetailInfo objAgeDetail in listAgeDetails)
                    {
                        DbCommand objAgeDetaildbCommand = DB.GetStoredProcCommand("FTradeAgeDetail_Add", FTradeSalesId, objAgeDetail.ShowIndex, objAgeDetail.TongJiName, objAgeDetail.ShowTitle, objAgeDetail.JinE, objAgeDetail.DaoQiRi, objAgeDetail.SuspendedMark, objAgeDetail.Remark, objAgeDetail.RealName, objAgeDetail.YearRate, objAgeDetail.TanQeChenBen, objAgeDetail.YuanQiDay);
                        int FTradeAgeId = Convert.ToInt32(DB.ExecuteScalar(objAgeDetaildbCommand, trans));
                    }
                    //4.历史记录,周报表
                    DbCommand FTHistorydbCommand = DB.GetStoredProcCommand("FTradeReceivablesHistory_CopyFromFTradeReceivables", FTradeID,"新增",0);
                    DB.ExecuteScalar(FTHistorydbCommand, trans);

                    trans.Commit();
                    return "";
                }
                catch (Exception exc)
                {
                    //发生异常，事务回滚
                    trans.Rollback();
                    return exc.Message;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
            
        }
        /// <summary>
        /// 删除应收帐款记录.
        ///1.需判断　7.冲帐记录(FTradeBalance)　　是否有　状态　为正常　的记录,如果有,则提示不能删除,只能先调帐再删除
        ///2.删除　机器维保费收入记录(FTradeUpKeep)
        ///3.删除　3.应收账款帐龄明细表(FTradeAgeDetail)
        ///4.删除　2.应收帐款销售类型更改记录表(FTradeSalesType)
        ///5.删除　1.应收账款主表(FTradeReceivables)
        /// </summary>
        /// <param name="FTradeID"></param>
        /// <returns></returns>
        public static string Delete(int FTradeID)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReceivables_Delete", FTradeID);
            try
            {
               return  DB.ExecuteScalar(dbCommand).ToString();
               
            }
            catch(Exception exc)
            {
                return exc.Message;
            }
        }

        public static IDataReader GetByFTradeID(int FTradeID)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReceivables_GetByFTradeID", FTradeID);
            return DB.ExecuteReader(dbCommand);
        }

        public static DataTableCollection GetPagination(string WhereSql, int PageIndex, int PageSize, string Sort, ref int TotalCount)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReceivables_GetPagination");
            DB.AddInParameter(dbCommand, "WhereSql", DbType.String, WhereSql);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String, Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32, 8);
            DataTableCollection dt = DB.ExecuteDataSet(dbCommand).Tables;

            TotalCount = Convert.ToInt32(DB.GetParameterValue(dbCommand, "TotalCount"));
            return dt;
        }
        /// <summary>
        /// 应收帐款基本信息的更新
        /// 如开票信息,终端信息,及基本信息,
        /// 更改销售类型不在此过程操作
        /// </summary>
        public static bool Update(FTradeReceivablesInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReceivables_Update", obj.FTradeID, obj.HeTongHao, obj.XiaoShouShiJian, obj.XingHao, obj.JiHao, obj.JingXiaoShang, obj.YingYeJingLi, obj.Price, obj.GPSFeiYong, obj.YueCuXiaoJinE, obj.HeTongJinE, obj.FuKuanTiaoKuan, obj.DaoQiRi, obj.PPKPRiQi, obj.PPKPHaoMa, obj.PPKPJinE, obj.ZPKPRiQi, obj.ZPKPHaoMa, obj.ZPKPJinE, obj.FaPiaoQianShouRen, obj.ZDXiaoShouState, obj.ZDYongHu, obj.ZDXiaoShouShiJian, obj.ZDShouJia, obj.GPSNum, obj.Currency, obj.CurrencyAmount, obj.Rate,obj.FanHuanLiXi,obj.InterestIncome);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch (Exception exc)
            {
                return false;
            }
        }
        /// <summary>
        /// 将返还利息 正常(未结算) =>正常(已结算)
        /// </summary>
        /// <param name="FTradeID"></param>
        /// <returns></returns>
        public static bool UpdateFanHuanLiXiState(int FTradeID, string FanHuanLiXiState)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReceivables_UpdateFanHuanLiXiState", FTradeID, FanHuanLiXiState);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch (Exception exc)
            {
                return false;
            }
        }

        /// <summary>
        /// 判断应收账款是否可删除,是否可变更销售类型
        /// </summary>
        /// <param name="FTradeID"></param>
        /// <returns></returns>
        public static string CheckCanChange(int FTradeID)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReceivables_CheckCanChange", FTradeID);
            try
            {
                return  DB.ExecuteScalar(dbCommand).ToString();              
            }
            catch(Exception exc)
            {
                return exc.Message.ToString();
            }
        }
        /// <summary>
        /// 添加或修改时,判断机号是否被使用,如果被使用则返回 错误信息
        /// </summary>
        /// <param name="JiHao"></param>
        /// <param name="FTradeID"></param>
        /// <returns></returns>
        public static string CheckJiHao(string JiHao,int FTradeID)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReceivables_CheckJiHao",JiHao, FTradeID);
            try
            {
                return DB.ExecuteScalar(dbCommand).ToString();
            }
            catch (Exception exc)
            {
                return exc.Message.ToString();
            }
        }
        /// <summary>
        /// 添加导入数据
        /// </summary>
        /// <param name="objReceivables">修改原有</param>
        /// <param name="objSalesType">销售类型</param>
        /// <param name="listAgeDetails">帐领</param>
        /// <returns></returns>
        public static string AddImport(FTradeReceivablesInfo objReceivables, FTradeSalesTypeInfo objSalesType, List<FTradeAgeDetailInfo> listAgeDetails)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    ////1.应收账款主表(FTradeReceivables)
                    //DbCommand objReceivablesdbCommand = DB.GetStoredProcCommand("FTradeReceivables_Add", objReceivables.HeTongHao, objReceivables.XiaoShouShiJian, objReceivables.XingHao, objReceivables.JiHao, objReceivables.JingXiaoShang, objReceivables.YingYeJingLi, objReceivables.Price, objReceivables.GPSFeiYong, objReceivables.YueCuXiaoJinE, objReceivables.HeTongJinE, objReceivables.FuKuanTiaoKuan, objReceivables.DaoQiRi, objReceivables.XiaoShouLeiXing, objReceivables.GPSNum, objReceivables.Currency, objReceivables.CurrencyAmount, objReceivables.Rate);
                    //int FTradeID = Convert.ToInt32(DB.ExecuteScalar(objReceivablesdbCommand, trans));//1.主表ID
                    //2.应收帐款销售类型更改记录表
                    DbCommand objSalesTypedbCommand = DB.GetStoredProcCommand("FTradeSalesType_Add", objReceivables.FTradeID, objSalesType.XiaoShouLeiXing, objSalesType.ExConfig, objSalesType.State, objSalesType.RealName);
                    int FTradeSalesId = Convert.ToInt32(DB.ExecuteScalar(objSalesTypedbCommand, trans));
                    //3.应收账款帐龄明细表(FTradeAgeDetail)
                    foreach (FTradeAgeDetailInfo objAgeDetail in listAgeDetails)
                    {
                        DbCommand objAgeDetaildbCommand = DB.GetStoredProcCommand("FTradeAgeDetail_Add", FTradeSalesId, objAgeDetail.ShowIndex, objAgeDetail.TongJiName, objAgeDetail.ShowTitle, objAgeDetail.JinE, objAgeDetail.DaoQiRi, objAgeDetail.SuspendedMark, objAgeDetail.Remark, objAgeDetail.RealName, objAgeDetail.YearRate, objAgeDetail.TanQeChenBen, objAgeDetail.YuanQiDay);
                        int FTradeAgeId = Convert.ToInt32(DB.ExecuteScalar(objAgeDetaildbCommand, trans));
                    }

                    trans.Commit();
                    return "";
                }
                catch (Exception exc)
                {
                    //发生异常，事务回滚
                    trans.Rollback();
                    return exc.Message;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }

        }

        /// <summary>
        /// 退机
        /// </summary>
        /// <param name="objNewFu">退机后负记录的信息</param>
        /// <param name="OldBalanceList">ID</param>
        /// <param name="objOld"></param>
        /// <returns>
        /// 大于0表示退机成功，并需要处理冲帐,大于0的数表示有多少条记录
        /// 等于0表示退机成功，但无法处理冲帐
        /// 小于表示退机失败
        /// </returns>
        public static int AddTuiJi(FTradeReceivablesInfo objNewFu, Dictionary<int, FTradeCatheringInfo> OldBalanceList, FTradeReceivablesInfo objOld, FTradeAgeDetailInfo objDetail)
        {
            
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    foreach (int FbalanceId in OldBalanceList.Keys)
                    {
                        int NewFCatheringId = 0;
                        FTradeCatheringInfo objCat = OldBalanceList[FbalanceId];
                        if (objCat.JinE > 0)
                        {
                            //1.添加系统的回收冲帐款记录
                            DbCommand dbCommandCather = DB.GetStoredProcCommand("FTradeCathering_Add", objCat.FCTitle, objCat.ShoukuanRiQi, objCat.JingXiaoShang, objCat.JinE, objCat.KuanXiangLeiXing, objCat.RealName, objCat.IsAdjustable, objCat.State, objCat.Remark, objCat.SourceTag, objCat.MoneyFrom, objCat.oldKuanXiangLeiXing);
                            NewFCatheringId = Convert.ToInt32(DB.ExecuteScalar(dbCommandCather, trans));
                        }
                        //2.收回原有冲帐款，如果有的话
                        if (FbalanceId > 0)
                        {
                            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeBalance_AddAdjusting", FbalanceId, NewFCatheringId);
                            DB.ExecuteScalar(dbCommand, trans);
                        }
                    }
                    

                    //2.改原记录状态及新增一条负记录
                    DbCommand dbCommChangeOPStep2 = DB.GetStoredProcCommand("FTradeReceivables_ChangeOPStep2", objNewFu.FTradeID, objNewFu.JiQiState, objNewFu.CreateDate, objNewFu.XiaoShouShiJian, objNewFu.Price, objNewFu.GPSFeiYong, objNewFu.YueCuXiaoJinE, objNewFu.HeTongJinE, objNewFu.FuKuanTiaoKuan);
                    int FuFTradeID = Convert.ToInt32(DB.ExecuteScalar(dbCommChangeOPStep2, trans));

                    //2013 09 24 处理退机 时仍有未收款项
                    if (objDetail.JinE > 0)
                    {
                        //3.添加未收记录
                        DbCommand objAgeDetaildbCommand = DB.GetStoredProcCommand("FTradeAgeDetail_Add", objDetail.FTradeSalesId, objDetail.ShowIndex, objDetail.TongJiName, objDetail.ShowTitle, objDetail.JinE, objDetail.DaoQiRi, objDetail.SuspendedMark, objDetail.Remark, objDetail.RealName, objDetail.YearRate, objDetail.TanQeChenBen, objDetail.YuanQiDay);
                        int FTradeAgeId = Convert.ToInt32(DB.ExecuteScalar(objAgeDetaildbCommand, trans));

                        //4.2013 09 24 处理退机 时仍有未收款项
                        DbCommand dbcommAddTuiJiWeiShouJinE = DB.GetStoredProcCommand("FTradeReceivables_AddTuiJiWeiShouJinE", objOld.FTradeID, objOld.HeTongJinE, objOld.YueCuXiaoJinE);
                        DB.ExecuteScalar(dbcommAddTuiJiWeiShouJinE, trans);
                    }

                    //4.历史记录,周报表
                    DbCommand FTHistorydbCommand = DB.GetStoredProcCommand("FTradeReceivablesHistory_CopyFromFTradeReceivables", FuFTradeID, "退机", 0);
                    DB.ExecuteScalar(FTHistorydbCommand, trans);
                    //throw new Exception("测试退机");
                    trans.Commit();
                    return OldBalanceList.Count();
                }
                catch
                {
                    //发生异常，事务回滚
                    trans.Rollback();
                    return -1;
                    //return exc.Message;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 换机,改分期，改经销商
        /// </summary>
        /// <param name="FbalanceIds">原冲帐ID</param>
        /// <param name="objCat">回收原冲帐信息的收款记录信息</param>
        /// <param name="objNewFu">换机后负记录的信息</param>
        /// <param name="objNew">新机信息</param>
        /// <param name="objSalesType"></param>
        /// <param name="listAgeDetails"></param>
        /// <returns>
        /// 大于0表示换机成功，并需要处理冲帐
        /// 等于0表示换机成功，但无法处理冲帐
        /// 小于表示换机失败
        /// </returns>
        public static int AddHuanJi(Dictionary<int, FTradeCatheringInfo> OldBalanceList, FTradeReceivablesInfo objNewFu, FTradeReceivablesInfo objNew, FTradeSalesTypeInfo objSalesType, List<FTradeAgeDetailInfo> listAgeDetails)
        {
           // int NewFCatheringId = 0;
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    foreach (int FbalanceId in OldBalanceList.Keys)
                    {
                        int NewFCatheringId = 0;
                        FTradeCatheringInfo objCat = OldBalanceList[FbalanceId];
                        if (objCat.JinE > 0)
                        {
                            //1.添加系统的回收冲帐款记录
                            DbCommand dbCommandCather = DB.GetStoredProcCommand("FTradeCathering_Add", objCat.FCTitle, objCat.ShoukuanRiQi, objCat.JingXiaoShang, objCat.JinE, objCat.KuanXiangLeiXing, objCat.RealName, objCat.IsAdjustable, objCat.State, objCat.Remark, objCat.SourceTag, objCat.MoneyFrom, objCat.oldKuanXiangLeiXing);
                            NewFCatheringId = Convert.ToInt32(DB.ExecuteScalar(dbCommandCather, trans));
                        }
                        //2.收回原有冲帐款，如果有的话
                        if (FbalanceId > 0)
                        {
                            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeBalance_AddAdjusting", FbalanceId, NewFCatheringId);
                            DB.ExecuteScalar(dbCommand, trans);
                        }
                    }

                    //2.改原记录状态及新增一条负记录
                    DbCommand dbCommChangeOPStep2 = DB.GetStoredProcCommand("FTradeReceivables_ChangeOPStep2", objNewFu.FTradeID, objNewFu.JiQiState, objNewFu.CreateDate, objNewFu.XiaoShouShiJian, objNewFu.Price, objNewFu.GPSFeiYong, objNewFu.YueCuXiaoJinE, objNewFu.HeTongJinE, objNewFu.FuKuanTiaoKuan);
                    int FuFTradeID = Convert.ToInt32(DB.ExecuteScalar(dbCommChangeOPStep2, trans));
                    //2.1.历史记录,周报表
                    DbCommand FTHistorydbCommand = DB.GetStoredProcCommand("FTradeReceivablesHistory_CopyFromFTradeReceivables", FuFTradeID, "换机分期改经销商负", 0);
                    DB.ExecuteScalar(FTHistorydbCommand, trans);

                    //3.换机后需要添加新机的信息
                    //3.1.应收账款主表(FTradeReceivables)
                    DbCommand objReceivablesdbCommand = DB.GetStoredProcCommand("FTradeReceivables_Add", objNew.HeTongHao, objNew.XiaoShouShiJian, objNew.XingHao, objNew.JiHao, objNew.JingXiaoShang, objNew.YingYeJingLi, objNew.Price, objNew.GPSFeiYong, objNew.YueCuXiaoJinE, objNew.HeTongJinE, objNew.FuKuanTiaoKuan, objNew.DaoQiRi, objNew.XiaoShouLeiXing, objNew.GPSNum, objNew.Currency, objNew.CurrencyAmount, objNew.Rate, objNew.ZDYongHu, objNew.FanHuanLiXi,objNew.InterestIncome);
                    int FTradeID = Convert.ToInt32(DB.ExecuteScalar(objReceivablesdbCommand, trans));//1.主表ID

                    //3.2.历史记录,周报表
                    DbCommand FTHistorydbCommandAdd = DB.GetStoredProcCommand("FTradeReceivablesHistory_CopyFromFTradeReceivables", FTradeID, "换机分期改经销商新", 0);
                    DB.ExecuteScalar(FTHistorydbCommandAdd, trans);

                    //3.2.应收帐款销售类型更改记录表
                    DbCommand objSalesTypedbCommand = DB.GetStoredProcCommand("FTradeSalesType_Add", FTradeID, objSalesType.XiaoShouLeiXing, objSalesType.ExConfig, objSalesType.State, objSalesType.RealName);
                    int FTradeSalesId = Convert.ToInt32(DB.ExecuteScalar(objSalesTypedbCommand, trans));
                    //3.3.应收账款帐龄明细表(FTradeAgeDetail)
                    foreach (FTradeAgeDetailInfo objAgeDetail in listAgeDetails)
                    {
                        DbCommand objAgeDetaildbCommand = DB.GetStoredProcCommand("FTradeAgeDetail_Add", FTradeSalesId, objAgeDetail.ShowIndex, objAgeDetail.TongJiName, objAgeDetail.ShowTitle, objAgeDetail.JinE, objAgeDetail.DaoQiRi, objAgeDetail.SuspendedMark, objAgeDetail.Remark, objAgeDetail.RealName, objAgeDetail.YearRate, objAgeDetail.TanQeChenBen, objAgeDetail.YuanQiDay);
                        int FTradeAgeId = Convert.ToInt32(DB.ExecuteScalar(objAgeDetaildbCommand, trans));
                    }


                    trans.Commit();
                    return OldBalanceList.Count();
                }
                catch
                {
                    //发生异常，事务回滚
                    trans.Rollback();
                    return -1;
                    //return exc.Message;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 变更合同条款
        /// </summary>
        /// <param name="FbalanceIds"></param>
        /// <param name="objCat"></param>
        /// <param name="objSalesType"></param>
        /// <param name="listAgeDetails"></param>
        /// <returns></returns>
        public static int AddChangeHeTong(Dictionary<int, FTradeCatheringInfo> OldBalanceList, FTradeReceivablesInfo objNewFu, FTradeSalesTypeInfo objSalesType, List<FTradeAgeDetailInfo> listAgeDetails)
        {
          
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    foreach (int FbalanceId in OldBalanceList.Keys)
                    {
                        int NewFCatheringId = 0;
                        FTradeCatheringInfo objCat = OldBalanceList[FbalanceId];
                        if (objCat.JinE > 0)
                        {
                            //1.添加系统的回收冲帐款记录
                            DbCommand dbCommandCather = DB.GetStoredProcCommand("FTradeCathering_Add", objCat.FCTitle, objCat.ShoukuanRiQi, objCat.JingXiaoShang, objCat.JinE, objCat.KuanXiangLeiXing, objCat.RealName, objCat.IsAdjustable, objCat.State, objCat.Remark, objCat.SourceTag, objCat.MoneyFrom, objCat.oldKuanXiangLeiXing);
                            NewFCatheringId = Convert.ToInt32(DB.ExecuteScalar(dbCommandCather, trans));
                        }
                        //2.收回原有冲帐款，如果有的话
                        if (FbalanceId > 0)
                        {
                            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeBalance_AddAdjusting", FbalanceId, NewFCatheringId);
                            DB.ExecuteScalar(dbCommand, trans);
                        }
                    }


                    //2.改原记录合同条款信息
                    DbCommand dbCommChangeOPStep2 = DB.GetStoredProcCommand("FTradeReceivables_ChangeOPUpdateHeTongStep2", objNewFu.FTradeID, objNewFu.HeTongHao, objNewFu.FuKuanTiaoKuan, objNewFu.CreateDate, objNewFu.FanHuanLiXi);
                    DB.ExecuteScalar(dbCommChangeOPStep2, trans);

                    //3.变更后需要处理冲帐及销售类型表的信息
                    ////3.1.应收账款主表(FTradeReceivables)
                    //DbCommand objReceivablesdbCommand = DB.GetStoredProcCommand("FTradeReceivables_Add", objNew.HeTongHao, objNew.XiaoShouShiJian, objNew.XingHao, objNew.JiHao, objNew.JingXiaoShang, objNew.YingYeJingLi, objNew.Price, objNew.GPSFeiYong, objNew.YueCuXiaoJinE, objNew.HeTongJinE, objNew.FuKuanTiaoKuan, objNew.DaoQiRi, objNew.XiaoShouLeiXing, objNew.GPSNum);
                    //int FTradeID = Convert.ToInt32(DB.ExecuteScalar(objReceivablesdbCommand, trans));//1.主表ID
                    //3.2.应收帐款销售类型更改记录表
                    DbCommand objSalesTypedbCommand = DB.GetStoredProcCommand("FTradeSalesType_Add", objNewFu.FTradeID, objSalesType.XiaoShouLeiXing, objSalesType.ExConfig, objSalesType.State, objSalesType.RealName);
                    int FTradeSalesId = Convert.ToInt32(DB.ExecuteScalar(objSalesTypedbCommand, trans));
                    //3.3.应收账款帐龄明细表(FTradeAgeDetail)
                    foreach (FTradeAgeDetailInfo objAgeDetail in listAgeDetails)
                    {
                        DbCommand objAgeDetaildbCommand = DB.GetStoredProcCommand("FTradeAgeDetail_Add", FTradeSalesId, objAgeDetail.ShowIndex, objAgeDetail.TongJiName, objAgeDetail.ShowTitle, objAgeDetail.JinE, objAgeDetail.DaoQiRi, objAgeDetail.SuspendedMark, objAgeDetail.Remark, objAgeDetail.RealName, objAgeDetail.YearRate, objAgeDetail.TanQeChenBen, objAgeDetail.YuanQiDay);
                        int FTradeAgeId = Convert.ToInt32(DB.ExecuteScalar(objAgeDetaildbCommand, trans));
                    }


                    trans.Commit();
                    return OldBalanceList.Count();
                }
                catch
                {
                    //发生异常，事务回滚
                    trans.Rollback();
                    return -1;
                    //return exc.Message;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
        }
       


        /// <summary>
        /// 退机
        /// </summary>
        /// <param name="objNewFu">退机后负记录的信息</param>
        /// <param name="FbalanceIds">原冲帐ID</param>
        /// <param name="objCat">回收原冲帐信息的收款记录信息</param>
        /// <returns>
        /// 大于0表示退机成功，并需要处理冲帐
        /// 等于0表示退机成功，但无法处理冲帐
        /// 小于表示退机失败
        /// </returns>
        public static int AddTuiJi(FTradeReceivablesInfo objNewFu, List<int> FbalanceIds, FTradeCatheringInfo objCat, FTradeReceivablesInfo objOld, FTradeAgeDetailInfo objDetail)
        {
            int NewFCatheringId=0;
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    if (objCat.JinE > 0)
                    {
                        //1.添加系统的回收冲帐款记录
                        DbCommand dbCommandCather = DB.GetStoredProcCommand("FTradeCathering_Add", objCat.FCTitle, objCat.ShoukuanRiQi, objCat.JingXiaoShang, objCat.JinE, objCat.KuanXiangLeiXing, objCat.RealName, objCat.IsAdjustable, objCat.State, objCat.Remark, objCat.SourceTag, objCat.MoneyFrom,objCat.oldKuanXiangLeiXing);
                        NewFCatheringId = Convert.ToInt32(DB.ExecuteScalar(dbCommandCather, trans));
                        //1.收回原有冲帐款，如果有的话
                        if (FbalanceIds.Count() > 0)
                        {
                            //处理已冲帐的记录
                            foreach (int FbalanceId in FbalanceIds)
                            {
                                DbCommand dbCommand = DB.GetStoredProcCommand("FTradeBalance_AddAdjusting", FbalanceId, NewFCatheringId);
                                DB.ExecuteScalar(dbCommand, trans);
                            }
                        }
                    }
                   
                    //2.改原记录状态及新增一条负记录
                    DbCommand dbCommChangeOPStep2 = DB.GetStoredProcCommand("FTradeReceivables_ChangeOPStep2", objNewFu.FTradeID, objNewFu.JiQiState, objNewFu.CreateDate, objNewFu.XiaoShouShiJian, objNewFu.Price, objNewFu.GPSFeiYong, objNewFu.YueCuXiaoJinE, objNewFu.HeTongJinE, objNewFu.FuKuanTiaoKuan);
                    int FuFTradeID = Convert.ToInt32(DB.ExecuteScalar(dbCommChangeOPStep2, trans));
                    
                    //2013 09 24 处理退机 时仍有未收款项
                    if (objDetail.JinE > 0)
                    {
                        //3.添加未收记录
                        DbCommand objAgeDetaildbCommand = DB.GetStoredProcCommand("FTradeAgeDetail_Add", objDetail.FTradeSalesId, objDetail.ShowIndex, objDetail.TongJiName, objDetail.ShowTitle, objDetail.JinE, objDetail.DaoQiRi, objDetail.SuspendedMark, objDetail.Remark, objDetail.RealName, objDetail.YearRate, objDetail.TanQeChenBen, objDetail.YuanQiDay);
                        int FTradeAgeId = Convert.ToInt32(DB.ExecuteScalar(objAgeDetaildbCommand, trans));

                        //4.2013 09 24 处理退机 时仍有未收款项
                        DbCommand dbcommAddTuiJiWeiShouJinE = DB.GetStoredProcCommand("FTradeReceivables_AddTuiJiWeiShouJinE", objOld.FTradeID, objOld.HeTongJinE, objOld.YueCuXiaoJinE);
                        DB.ExecuteScalar(dbcommAddTuiJiWeiShouJinE, trans);
                    }

                    //4.历史记录,周报表
                    DbCommand FTHistorydbCommand = DB.GetStoredProcCommand("FTradeReceivablesHistory_CopyFromFTradeReceivables", FuFTradeID, "退机", 0);
                    DB.ExecuteScalar(FTHistorydbCommand, trans);
                    //throw new Exception("测试退机");
                    trans.Commit();
                    return NewFCatheringId;
                }
                catch
                {
                    //发生异常，事务回滚
                    trans.Rollback();
                    return -1;
                    //return exc.Message;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 换机,改分期，改经销商
        /// </summary>
        /// <param name="FbalanceIds">原冲帐ID</param>
        /// <param name="objCat">回收原冲帐信息的收款记录信息</param>
        /// <param name="objNewFu">换机后负记录的信息</param>
        /// <param name="objNew">新机信息</param>
        /// <param name="objSalesType"></param>
        /// <param name="listAgeDetails"></param>
        /// <returns>
        /// 大于0表示换机成功，并需要处理冲帐
        /// 等于0表示换机成功，但无法处理冲帐
        /// 小于表示换机失败
        /// </returns>
        public static int AddHuanJi(List<int> FbalanceIds, FTradeCatheringInfo objCat,FTradeReceivablesInfo objNewFu,FTradeReceivablesInfo objNew, FTradeSalesTypeInfo objSalesType, List<FTradeAgeDetailInfo> listAgeDetails)
        {
            int NewFCatheringId = 0;
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    if (objCat.JinE > 0)
                    {
                        //1.添加系统的回收冲帐款记录
                        DbCommand dbCommandCather = DB.GetStoredProcCommand("FTradeCathering_Add", objCat.FCTitle, objCat.ShoukuanRiQi, objCat.JingXiaoShang, objCat.JinE, objCat.KuanXiangLeiXing, objCat.RealName, objCat.IsAdjustable, objCat.State, objCat.Remark, objCat.SourceTag, objCat.MoneyFrom, objCat.oldKuanXiangLeiXing);
                        NewFCatheringId = Convert.ToInt32(DB.ExecuteScalar(dbCommandCather, trans));
                        //1.收回原有冲帐款，如果有的话
                        if (FbalanceIds.Count() > 0)
                        {
                            //处理已冲帐的记录
                            foreach (int FbalanceId in FbalanceIds)
                            {
                                DbCommand dbCommand = DB.GetStoredProcCommand("FTradeBalance_AddAdjusting", FbalanceId, NewFCatheringId);
                                DB.ExecuteScalar(dbCommand, trans);
                            }
                        }

                    }
                   
                    //2.改原记录状态及新增一条负记录
                    DbCommand dbCommChangeOPStep2 = DB.GetStoredProcCommand("FTradeReceivables_ChangeOPStep2", objNewFu.FTradeID, objNewFu.JiQiState, objNewFu.CreateDate, objNewFu.XiaoShouShiJian, objNewFu.Price, objNewFu.GPSFeiYong, objNewFu.YueCuXiaoJinE, objNewFu.HeTongJinE, objNewFu.FuKuanTiaoKuan);
                    int FuFTradeID=Convert.ToInt32( DB.ExecuteScalar(dbCommChangeOPStep2, trans));
                    //2.1.历史记录,周报表
                    DbCommand FTHistorydbCommand = DB.GetStoredProcCommand("FTradeReceivablesHistory_CopyFromFTradeReceivables", FuFTradeID, "换机分期改经销商负", 0);
                    DB.ExecuteScalar(FTHistorydbCommand, trans);

                    //3.换机后需要添加新机的信息
                    //3.1.应收账款主表(FTradeReceivables)
                    DbCommand objReceivablesdbCommand = DB.GetStoredProcCommand("FTradeReceivables_Add", objNew.HeTongHao, objNew.XiaoShouShiJian, objNew.XingHao, objNew.JiHao, objNew.JingXiaoShang, objNew.YingYeJingLi, objNew.Price, objNew.GPSFeiYong, objNew.YueCuXiaoJinE, objNew.HeTongJinE, objNew.FuKuanTiaoKuan, objNew.DaoQiRi, objNew.XiaoShouLeiXing, objNew.GPSNum, objNew.Currency, objNew.CurrencyAmount, objNew.Rate,objNew.ZDYongHu,objNew.FanHuanLiXi);
                    int FTradeID = Convert.ToInt32(DB.ExecuteScalar(objReceivablesdbCommand, trans));//1.主表ID

                    //3.2.历史记录,周报表
                    DbCommand FTHistorydbCommandAdd = DB.GetStoredProcCommand("FTradeReceivablesHistory_CopyFromFTradeReceivables", FTradeID, "换机分期改经销商新", 0);
                    DB.ExecuteScalar(FTHistorydbCommandAdd, trans);

                    //3.2.应收帐款销售类型更改记录表
                    DbCommand objSalesTypedbCommand = DB.GetStoredProcCommand("FTradeSalesType_Add", FTradeID, objSalesType.XiaoShouLeiXing, objSalesType.ExConfig, objSalesType.State, objSalesType.RealName);
                    int FTradeSalesId = Convert.ToInt32(DB.ExecuteScalar(objSalesTypedbCommand, trans));
                    //3.3.应收账款帐龄明细表(FTradeAgeDetail)
                    foreach (FTradeAgeDetailInfo objAgeDetail in listAgeDetails)
                    {
                        DbCommand objAgeDetaildbCommand = DB.GetStoredProcCommand("FTradeAgeDetail_Add", FTradeSalesId, objAgeDetail.ShowIndex, objAgeDetail.TongJiName, objAgeDetail.ShowTitle, objAgeDetail.JinE, objAgeDetail.DaoQiRi, objAgeDetail.SuspendedMark, objAgeDetail.Remark, objAgeDetail.RealName, objAgeDetail.YearRate, objAgeDetail.TanQeChenBen, objAgeDetail.YuanQiDay);
                        int FTradeAgeId = Convert.ToInt32(DB.ExecuteScalar(objAgeDetaildbCommand, trans));
                    }


                    trans.Commit();
                    return NewFCatheringId;
                }
                catch
                {
                    //发生异常，事务回滚
                    trans.Rollback();
                    return -1;
                    //return exc.Message;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
        }


        /// <summary>
        /// 变更合同条款
        /// </summary>
        /// <param name="FbalanceIds"></param>
        /// <param name="objCat"></param>
        /// <param name="objSalesType"></param>
        /// <param name="listAgeDetails"></param>
        /// <returns></returns>
        public static int AddChangeHeTong(List<int> FbalanceIds, FTradeCatheringInfo objCat, FTradeReceivablesInfo objNewFu, FTradeSalesTypeInfo objSalesType, List<FTradeAgeDetailInfo> listAgeDetails)
        {
            int NewFCatheringId = 0;
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    //2013 10 29 修改
                    //1.添加系统的回收冲帐款记录
                    if (objCat.JinE > 0)
                    {
                        DbCommand dbCommandCather = DB.GetStoredProcCommand("FTradeCathering_Add", objCat.FCTitle, objCat.ShoukuanRiQi, objCat.JingXiaoShang, objCat.JinE, objCat.KuanXiangLeiXing, objCat.RealName, objCat.IsAdjustable, objCat.State, objCat.Remark, objCat.SourceTag, objCat.MoneyFrom, objCat.oldKuanXiangLeiXing);
                        NewFCatheringId = Convert.ToInt32(DB.ExecuteScalar(dbCommandCather, trans));

                        //1.收回原有冲帐款，如果有的话
                        if (FbalanceIds.Count() > 0)
                        {
                            //处理已冲帐的记录
                            foreach (int FbalanceId in FbalanceIds)
                            {
                                DbCommand dbCommand = DB.GetStoredProcCommand("FTradeBalance_AddAdjusting", FbalanceId, NewFCatheringId);
                                DB.ExecuteScalar(dbCommand, trans);
                            }
                        }

                    }
                    

                    //2.改原记录合同条款信息
                    DbCommand dbCommChangeOPStep2 = DB.GetStoredProcCommand("FTradeReceivables_ChangeOPUpdateHeTongStep2", objNewFu.FTradeID,objNewFu.HeTongHao, objNewFu.FuKuanTiaoKuan, objNewFu.CreateDate,objNewFu.FanHuanLiXi);
                    DB.ExecuteScalar(dbCommChangeOPStep2, trans);

                    //3.变更后需要处理冲帐及销售类型表的信息
                    ////3.1.应收账款主表(FTradeReceivables)
                    //DbCommand objReceivablesdbCommand = DB.GetStoredProcCommand("FTradeReceivables_Add", objNew.HeTongHao, objNew.XiaoShouShiJian, objNew.XingHao, objNew.JiHao, objNew.JingXiaoShang, objNew.YingYeJingLi, objNew.Price, objNew.GPSFeiYong, objNew.YueCuXiaoJinE, objNew.HeTongJinE, objNew.FuKuanTiaoKuan, objNew.DaoQiRi, objNew.XiaoShouLeiXing, objNew.GPSNum);
                    //int FTradeID = Convert.ToInt32(DB.ExecuteScalar(objReceivablesdbCommand, trans));//1.主表ID
                    //3.2.应收帐款销售类型更改记录表
                    DbCommand objSalesTypedbCommand = DB.GetStoredProcCommand("FTradeSalesType_Add", objNewFu.FTradeID, objSalesType.XiaoShouLeiXing, objSalesType.ExConfig, objSalesType.State, objSalesType.RealName);
                    int FTradeSalesId = Convert.ToInt32(DB.ExecuteScalar(objSalesTypedbCommand, trans));
                    //3.3.应收账款帐龄明细表(FTradeAgeDetail)
                    foreach (FTradeAgeDetailInfo objAgeDetail in listAgeDetails)
                    {
                        DbCommand objAgeDetaildbCommand = DB.GetStoredProcCommand("FTradeAgeDetail_Add", FTradeSalesId, objAgeDetail.ShowIndex, objAgeDetail.TongJiName, objAgeDetail.ShowTitle, objAgeDetail.JinE, objAgeDetail.DaoQiRi, objAgeDetail.SuspendedMark, objAgeDetail.Remark, objAgeDetail.RealName, objAgeDetail.YearRate, objAgeDetail.TanQeChenBen, objAgeDetail.YuanQiDay);
                        int FTradeAgeId = Convert.ToInt32(DB.ExecuteScalar(objAgeDetaildbCommand, trans));
                    }


                    trans.Commit();
                    return NewFCatheringId;
                }
                catch
                {
                    //发生异常，事务回滚
                    trans.Rollback();
                    return -1;
                    //return exc.Message;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
        }
       
        /// <summary>
        /// 处理调整GPS费用
        /// </summary>
        /// <param name="FTradeID"></param>
        /// <param name="FTradeAgeId">最后一条ID</param>
        /// <param name="NewGPSFeiYong"></param>
        /// <param name="deleteJinE"></param>
        /// <param name="State"></param>
        /// <param name="Remark"></param>
        /// <returns></returns>
        public static int UpdateGPSFeiYong(int FTradeID, int FTradeAgeId, decimal NewGPSFeiYong, decimal deleteJinE, string State, string Remark, FTradeCatheringInfo objCat, decimal weekreportJinE)
        { 
            int NewFCatheringId = 0;
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    if (objCat.JinE > 0)
                    {
                        DbCommand dbCommandCather = DB.GetStoredProcCommand("FTradeCathering_Add", objCat.FCTitle, objCat.ShoukuanRiQi, objCat.JingXiaoShang, objCat.JinE, objCat.KuanXiangLeiXing, objCat.RealName, objCat.IsAdjustable, objCat.State, objCat.Remark, objCat.SourceTag, objCat.MoneyFrom, objCat.oldKuanXiangLeiXing);
                        NewFCatheringId = Convert.ToInt32(DB.ExecuteScalar(dbCommandCather, trans));
                        objCat.FCatheringId = NewFCatheringId;
                    }
                    //变更
                    DbCommand UpdateGPSFeiYongCommand = DB.GetStoredProcCommand("FTradeReceivables_UpdateGPSFeiYong", FTradeID, FTradeAgeId, NewGPSFeiYong, deleteJinE, State, Remark);
                    DB.ExecuteScalar(UpdateGPSFeiYongCommand, trans);
                    //4.历史记录,周报表
                    DbCommand FTHistorydbCommandAdd = DB.GetStoredProcCommand("FTradeReceivablesHistory_CopyFromFTradeReceivables", FTradeID, "退GPS", weekreportJinE);
                    DB.ExecuteScalar(FTHistorydbCommandAdd, trans);

                    trans.Commit();
                    return NewFCatheringId;
                }
                catch
                {
                    //发生异常，事务回滚
                    trans.Rollback();
                    return -1;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
        }

        #region 操作历史相关

        public static DataTableCollection FTradeReceivablesHistoryGetPagination(string WhereSql, int PageIndex, int PageSize, string Sort, ref int TotalCount)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReceivablesHistory_GetPagination");
            DB.AddInParameter(dbCommand, "WhereSql", DbType.String, WhereSql);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String, Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32, 8);
            DataTableCollection dt = DB.ExecuteDataSet(dbCommand).Tables;

            TotalCount = Convert.ToInt32(DB.GetParameterValue(dbCommand, "TotalCount"));
            return dt;
        }

        #endregion
        /// <summary>
        /// 获取透视表数据
        /// </summary>
        /// <param name="WhereSql"></param>
        /// <returns></returns>
        public static DataTableCollection GetPivotTable(string WhereSql)
        {
            try
            {
                Database DB = DatabaseFactory.CreateDatabase();

                DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReceivables_GetPivotTable");
                DB.AddInParameter(dbCommand, "WhereSql", DbType.String, WhereSql);

                DataTableCollection dt = DB.ExecuteDataSet(dbCommand).Tables;

                return dt;
            }
            catch
            {
                throw new Exception(WhereSql);
            }
        }

        /// <summary>
        /// 经销商机型销售明细表
        /// </summary>
        /// <param name="WhereSql"></param>
        /// <returns></returns>
        public static DataTableCollection GetPivotTable2(string WhereSql)
        {
            try
            {
                Database DB = DatabaseFactory.CreateDatabase();

                DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReceivables_GetPivotTable2");
                DB.AddInParameter(dbCommand, "WhereSql", DbType.String, WhereSql);

                DataTableCollection dt = DB.ExecuteDataSet(dbCommand).Tables;

                return dt;
            }
            catch
            {
                throw new Exception(WhereSql);
            }
        }
        /// <summary>
        /// 更新合同原件是否收到
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static Boolean UpdateYuanJian(FTradeReceivablesInfo obj)
        {
            try
            {
                Database DB = DatabaseFactory.CreateDatabase();
                DbCommand UpdateGPSFeiYongCommand = DB.GetStoredProcCommand("FTradeReceivables_UpdateYuanJian", obj.FTradeID, obj.HeTongYuanJian, obj.HeTongYuanJianDate, obj.ShouHouYuanJian, obj.ShouHouYuanJianDate);
                DB.ExecuteScalar(UpdateGPSFeiYongCommand);

                return true;
            }
            catch
            {
                return false;
               // throw new Exception(WhereSql);
            }
        }
    }
}
